﻿using Npgsql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Windows.Forms;

namespace AutoCreateCode
{
    public partial class frmcreatecode : Form
    {
        ConfigDatabase.Class.ConnectDatabase _condata;
        DataTable _dttable;
        DataTable _dtcolumn;
        DataSet _dsxml;
        public frmcreatecode()
        {
            InitializeComponent();
            f_create_xml(false);
        }
        void f_create_xml(bool bwrite)
        {
            try
            {
                _dsxml = new DataSet();
                _dsxml.ReadXml(System.IO.Directory.GetCurrentDirectory()+ "\\current.xml", XmlReadMode.ReadSchema);
                string vstam = "";
                vstam = _dsxml.Tables[0].Rows[0]["duongdan"].ToString();
                vstam = _dsxml.Tables[0].Rows[0]["namespace"].ToString();
            }
            catch 
            {
                _dsxml = new DataSet();
                _dsxml.Tables.Add();
                _dsxml.Tables[0].Columns.Add("duongdan");
                _dsxml.Tables[0].Columns.Add("namespace");
                DataRow vdr = _dsxml.Tables[0].NewRow();
                vdr[0] = System.IO.Directory.GetCurrentDirectory();
                vdr[1] = "baseclass";
                _dsxml.Tables[0].Rows.Add(vdr);
            }
            
            if (bwrite)
            {
                _dsxml.Tables[0].Clear();
                DataRow vdr = _dsxml.Tables[0].NewRow();
                vdr[0] = txtduongdan.Text;
                vdr[1] = txtnamespace.Text;
                _dsxml.Tables[0].Rows.Add(vdr);
               
            }
            else
            {
                txtduongdan.Text = _dsxml.Tables[0].Rows[0][0].ToString();
                txtnamespace.Text = _dsxml.Tables[0].Rows[0][1].ToString();
            }
            _dsxml.WriteXml(System.IO.Directory.GetCurrentDirectory() + "\\current.xml", XmlWriteMode.WriteSchema);
        }
        private void frmcreatecode_Load(object sender, EventArgs e)
        {
            _condata = new ConfigDatabase.Class.ConnectDatabase();
            f_load_schema();
        }

        private void btndatabase_Click(object sender, EventArgs e)
        {
            ConfigDatabase.Forms.frmchondatabase frm = new ConfigDatabase.Forms.frmchondatabase();
            frm.ShowDialog();
            _condata = new ConfigDatabase.Class.ConnectDatabase();
            f_load_schema();
           
        }
        void f_load_table()
        {
            try
            {
                _dttable = new DataTable();
                string vsql = "select table_name,table_schema from information_schema.tables"
                   + " where table_catalog='" + _condata.Database + "' and table_schema='" + cbbschema.SelectedValue.ToString() + "'"
                   +" order by table_name";
                _dttable = f_getdata(vsql);
                clbtable.DataSource = _dttable;
                clbtable.DisplayMember = "table_name";
                clbtable.ValueMember = "table_name";
                vsql = "select  table_name as table,column_name as col,data_type as typ,character_maximum_length as charlen,numeric_precision as numlen,numeric_scale as sole,datetime_precision as dat"
                    + ",column_name||' ('||data_type||')' as col_name"
                + " from  information_schema.columns"
                + " where table_catalog='"+_condata.Database+"'"
                + " and table_schema='"+cbbschema.SelectedValue.ToString()+"'"                
                + " order by table_name,ordinal_position";
                _dtcolumn = f_getdata(vsql);
            }
            catch { }
        }
        void f_load_schema()
        {
            try
            {
                DataTable vdttable = new DataTable();
                string vsql = "select schema_name as table_name from information_schema.schemata"
                   + " where catalog_name='" + _condata.Database + "' order by schema_name";
                vdttable = f_getdata(vsql);
                cbbschema.DataSource = vdttable;
                cbbschema.DisplayMember = "table_name";
                cbbschema.ValueMember = "table_name";
            }
            catch { }
        }
        DataTable f_getdata(string sql)
        {
            DataTable vdttam = new DataTable();
            NpgsqlConnection vconn = new NpgsqlConnection(_condata.StringConnect);
            try
            {
                vconn.Open();
                NpgsqlDataAdapter vadap = new NpgsqlDataAdapter(sql, vconn);
                vadap.Fill(vdttam);
            }
            catch { }
            finally
            {
                vconn.Close();
            }
            return vdttam;
        }

        private void cbbschema_SelectedValueChanged(object sender, EventArgs e)
        {
            f_load_table();
        }

        private void clbtable_SelectedValueChanged(object sender, EventArgs e)
        {
            try
            {
                
                lbxcolumn.DataSource = f_getDataColumn(clbtable.SelectedValue.ToString());
                lbxcolumn.DisplayMember = "col_name";
                lbxcolumn.ValueMember = "col";
            }
            catch { }
        }
        DataTable f_getDataColumn(string TableName)
        {
            DataTable vdtcol;
            DataView vdtview = _dtcolumn.DefaultView;
            vdtview.RowFilter = "table='" + TableName + "'";
            vdtview.Sort = "table";
            vdtcol = vdtview.ToTable();
            return vdtcol;
        }
        private void btnduongdan_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog dia = new FolderBrowserDialog();
            dia.ShowNewFolderButton = true;
            if (txtduongdan.Text == "")
            {
                dia.SelectedPath = System.IO.Directory.GetCurrentDirectory();
            }
            else
            {
                dia.SelectedPath = txtduongdan.Text;
            }
            dia.ShowDialog();
            string vpath = dia.SelectedPath;
            if (vpath != "")
            {
                txtduongdan.Text = vpath;
                //txtnamespace.Text=vpath.
            }
        }

        private void btntao_Click(object sender, EventArgs e)
        {
            string vnamespace = txtnamespace.Text;
            string vpath = txtduongdan.Text;
            if (clbtable.CheckedItems.Count == 0)
            {
                MessageBox.Show("Chọn table cần tạo code.", "Thông báo");
                clbtable.Focus();
                return;
            }
            if (vnamespace == "")
            {
                MessageBox.Show("Nhập namespace.", "Thông báo");
                txtnamespace.Focus();
                return;
            }
            if (!System.IO.Directory.Exists(vpath))
            {
                if (MessageBox.Show("Đường dẫn này " + vpath + " không tồn tại.\nBạn có muốn tạo đường dẫn này không?", "Thông báo", MessageBoxButtons.YesNoCancel) == DialogResult.Yes)
                {
                    System.IO.Directory.CreateDirectory(vpath);
                }
                else
                {
                    MessageBox.Show("Tạo không thành công.", "Thông báo");
                    return;
                }
            }
            foreach (DataRowView vdrv in clbtable.CheckedItems)
            {
                string vsTable = vdrv[clbtable.ValueMember].ToString();
                DataTable vdtCol = f_getDataColumn(vsTable);
                f_createCode(vsTable,vdtCol);
                vdtCol.Dispose();
            }
            MessageBox.Show("Đã tạo xong.", "Thông báo");
        }
        void f_createCode(string tableName,DataTable dtcolumn)
        {
            string[] varrcap = { "\r\n", "\r\n\t", "\r\n\t\t", "\r\n\t\t\t", "\r\n\t\t\t\t" };
            System.IO.StreamWriter vsw = new System.IO.StreamWriter(txtduongdan.Text+"\\"+tableName + ".cs");
            string vtext = "";
            string vdto="dto"+tableName.ToUpper();
            vtext = "using System.Data;"
                +varrcap[0]+"using Npgsql;"
                + varrcap[0] + "using NpgsqlTypes;"
                + varrcap[0] + "using ConfigDatabase.Class;\r\n"
                + varrcap[0] + "namespace "+txtnamespace.Text
                + varrcap[0] + "{"
                + varrcap[1] + "public class "+vdto
                + varrcap[1] + "{"
                + varrcap[2] + "ConnectDatabase _condata;"
                + varrcap[2] + "NpgsqlConnection _connect;"
                + varrcap[2] + "string _sSchema;"
                + varrcap[2] + "#region column";
            vsw.Write(vtext); vtext = "";

            #region column
            foreach (DataRow vdr in dtcolumn.Rows)
            {
                vtext += varrcap[2] + "public static string column" + vdr["col"].ToString().ToUpper()
                        + varrcap[2] + "{"
                            + varrcap[3] + "get{ return \"" + vdr["col"].ToString() + "\";}"
                        + varrcap[2] + "}";
            }
             vtext += varrcap[2] + "public static string tableName"
                        + varrcap[2] + "{"
                            + varrcap[3] + "get{ return \"" + tableName + "\";}"
                        + varrcap[2] + "}";
            vtext += varrcap[2] + "#endregion";
            vsw.Write(vtext); vtext = "";
            #endregion

            #region property
            vtext += varrcap[2] + "#region property";
            try { dtcolumn.Columns.Add("value"); }
            catch { }
            try { dtcolumn.Columns.Add("valuetype"); }
            catch { }
            foreach (DataRow vdr in dtcolumn.Rows)
            {
                if (vdr["sole"].ToString() == ""&&vdr["numlen"].ToString() == "")
                {
                    vdr["value"] = "_s" + vdr["col"].ToString();
                    vdr["valuetype"] = "string";
                    
                }
                else if (vdr["sole"].ToString() != "0")
                {
                    vdr["value"] = "_de" + vdr["col"].ToString();
                    vdr["valuetype"] = "decimal";

                }
                else if (Convert.ToInt32(vdr["numlen"].ToString()) > 8)
                {
                    vdr["value"] = "_l" + vdr["col"].ToString();
                    vdr["valuetype"] = "long";
                }
                else
                {
                    vdr["value"] = "_i" + vdr["col"].ToString();
                    vdr["valuetype"] = "int";
                }
                vtext += varrcap[2] + vdr["valuetype"].ToString() + " " + vdr["value"].ToString() + ";";
                vtext += varrcap[2] + "public "+vdr["valuetype"].ToString()+" p" + vdr["col"].ToString().ToUpper()
                        + varrcap[2] + "{"
                            + varrcap[3] + "get{ return " + vdr["value"].ToString() + ";}"
                            + varrcap[3] + "set{  " + vdr["value"].ToString() + " = value;}"
                        + varrcap[2] + "}";
            }
            vtext += varrcap[2] + "#endregion";
            vsw.Write(vtext); vtext = "";
            #endregion

            #region khoitao
            vtext += varrcap[2] + "public dto" + tableName.ToUpper() + "()"
                    + varrcap[2] + "{"
                        + varrcap[3] + "this.KhoiTao();"
                        + varrcap[3] + "this._condata = new ConnectDatabase();"
                        + varrcap[3] + "this._sSchema = this._condata.Schema;"
                    + varrcap[2] + "}"
                    + varrcap[2] + "public dto" + tableName.ToUpper() + "(ConnectDatabase conn)"
                   + varrcap[2] + "{"
                       + varrcap[3] + "this.KhoiTao();"
                       + varrcap[3] + "this._condata = conn;"
                       + varrcap[3] + "this._sSchema = this._condata.Schema;"
                   + varrcap[2] + "}"
                   + varrcap[2] + "void KhoiTao()"
                   + varrcap[2] + "{";
            foreach (DataRow vdr in dtcolumn.Rows)
            {
                if (vdr["valuetype"].ToString() == "string")
                {
                    vtext += varrcap[3] + "this." + vdr["value"].ToString() + " = \"\";";
                }
                else
                {
                    vtext += varrcap[3] + "this." + vdr["value"].ToString() + " = 0;";
                }
            }
            vtext += varrcap[2] + "}";
            vsw.Write(vtext); vtext = "";
            #endregion

            #region insert
            vtext += varrcap[2] + "public bool Insert()"
                 + varrcap[2] + "{"
                    + varrcap[3] + "bool flag;"
                     + varrcap[3] + "try"
                     + varrcap[3] + "{"
                         + varrcap[4] + "this._connect = new NpgsqlConnection(this._condata.StringConnect);"
                         + varrcap[4] + "this._connect.Open();"
                         + varrcap[4] + "flag = this.Insert(this._connect);"
                     + varrcap[3] + "}"
                     + varrcap[3] + "catch (NpgsqlException exception)"
                     + varrcap[3] + "{"
                         + varrcap[4] + "ConnectDatabase.TrackingError(exception.ToString(), \"Insert()\", \"" + vdto + ".cs\");"
                         + varrcap[4] + "flag = false;"
                     + varrcap[3] + "}"
                     + varrcap[3] + "finally"
                     + varrcap[3] + "{"
                         + varrcap[4] + "this._connect.Close();"
                         + varrcap[4] + "this._connect.Dispose();"
                     + varrcap[3] + "}"
                     + varrcap[3] + "return flag;"
                 + varrcap[2] + "}";
            vsw.Write(vtext); vtext = "";
            vtext += varrcap[2] + "public bool Insert(NpgsqlConnection connect)"
                 + varrcap[2] + "{"
                     + varrcap[3] + "try"
                     + varrcap[3] + "{"
                        + varrcap[4] + "string vsql = \"insert into \" + this._sSchema + \".\" + tableName + \"( \"";
            string vsInsCol = "", vsInsVal = "", vsInsPar = "";
            foreach (DataRow vdr in dtcolumn.Rows)
            {
                vsInsCol += " + \",\" + column"+vdr["col"].ToString().ToUpper();
                if (vdr["numlen"].ToString() != "" && vdr["sole"].ToString() != "")
                {
                    vsInsVal += " + \",\" + " + vdr["value"].ToString();
                }
                else if (vdr["dat"].ToString() != "")
                {
                    vsInsVal +=" + \",to_timestamp('\" + "+ vdr["value"].ToString() + " + \"','dd/mm/yyyy hh24:mi')\"";                    
                }
                else if(vdr["charlen"].ToString()!="")
                {
                    vsInsVal += " + \",'\" + "+vdr["value"].ToString() + " + \"'\"";                    
                }
                else
                {
                    vsInsVal +=" + \",:\" + column"+ vdr["col"].ToString().ToUpper();
                    vsInsPar += "\r\ncommand.Parameters.Add(column"+vdr["col"].ToString().ToUpper()+", NpgsqlDbType.Text, 200).Value = this."+vdr["value"].ToString()+";";
                }
            }
            vtext += varrcap[4] + vsInsCol.Replace(" + \",\"", "#").Trim('#').Replace("#", " + \",\"") + " + \")\""
                + varrcap[4] + " + \" values(\"" + vsInsVal.Replace(" + \",\"", "#").Trim('#').Replace("#", " + \",\"") + " + \")\";";
            vtext += varrcap[4] + "NpgsqlCommand command = new NpgsqlCommand(vsql, connect);"
                        + varrcap[4] + vsInsPar.Replace("\r\n","#").Trim('#').Replace("#",varrcap[4])
                         + varrcap[4] + "return (command.ExecuteNonQuery() != 0);"
                     + varrcap[3] + "}"
                     + varrcap[3] + "catch (NpgsqlException exception)"
                     + varrcap[3] + "{"
                         + varrcap[4] + "ConnectDatabase.TrackingError(exception.ToString(), \"Insert(NpgsqlConnection connect)\", \"" + vdto + ".cs\");"
                         + varrcap[4] + "return false;"
                     + varrcap[3] + "}"                     
                 + varrcap[2] + "}";
            vsw.Write(vtext); vtext = "";
            #endregion

            #region update
            vtext += varrcap[2] + "public bool Update()"
                 + varrcap[2] + "{"
                    + varrcap[3] + "bool flag;"
                     + varrcap[3] + "try"
                     + varrcap[3] + "{"
                         + varrcap[4] + "this._connect = new NpgsqlConnection(this._condata.StringConnect);"
                         + varrcap[4] + "this._connect.Open();"
                         + varrcap[4] + "flag = this.Update(this._connect);"
                     + varrcap[3] + "}"
                     + varrcap[3] + "catch (NpgsqlException exception)"
                     + varrcap[3] + "{"
                         + varrcap[4] + "ConnectDatabase.TrackingError(exception.ToString(), \"Update()\", \"" + vdto + ".cs\");"
                         + varrcap[4] + "flag = false;"
                     + varrcap[3] + "}"
                     + varrcap[3] + "finally"
                     + varrcap[3] + "{"
                         + varrcap[4] + "this._connect.Close();"
                         + varrcap[4] + "this._connect.Dispose();"
                     + varrcap[3] + "}"
                     + varrcap[3] + "return flag;"
                 + varrcap[2] + "}";
            vsw.Write(vtext); vtext = "";
            vtext += varrcap[2] + "public bool Update(NpgsqlConnection connect)"
                 + varrcap[2] + "{"
                     + varrcap[3] + "try"
                     + varrcap[3] + "{"
                        + varrcap[4] + "string vsql = \"update \" + this._sSchema + \".\" + tableName + \" set \"";
            vsInsCol = ""; vsInsPar = "";
            foreach (DataRow vdr in dtcolumn.Rows)
            {
                
                if (vdr["numlen"].ToString() != "" && vdr["sole"].ToString() != "")
                {
                    vsInsVal = "\" + " + vdr["value"].ToString() + " + \",\"";
                }
                else if (vdr["dat"].ToString() != "")
                {
                    vsInsVal = "to_timestamp('\" + " + vdr["value"].ToString() + " + \"','dd/mm/yyyy hh24:mi'),\"";
                }
                else if (vdr["charlen"].ToString() != "")
                {
                    vsInsVal = "'\" + " + vdr["value"].ToString() + " + \"',\"";
                }
                else
                {
                    vsInsVal = ":\" + column" + vdr["col"].ToString().ToUpper() + " + \",\"";
                    vsInsPar += "\r\ncommand.Parameters.Add(column" + vdr["col"].ToString().ToUpper() + ", NpgsqlDbType.Text, 200).Value = this." + vdr["value"].ToString() + ";";
                }
                vsInsCol += " + column" + vdr["col"].ToString().ToUpper() + " + \" = "+vsInsVal;
            }
            vsInsVal = vsInsCol.Replace(" + \",\"", "#").Trim('#').Replace("#", " + \",\"");
            if (vsInsVal.Substring(vsInsVal.Length - 3) == "',\"" || vsInsVal.Substring(vsInsVal.Length - 3) == "),\"")
            {
                vsInsVal = vsInsVal.Substring(0,vsInsVal.Length - 2) + "\"";
            }
            vtext += varrcap[4] + vsInsVal + " + \" where 1=1\";";
            vtext += varrcap[4] + "NpgsqlCommand command = new NpgsqlCommand(vsql, connect);"
                        + varrcap[4] + vsInsPar.Replace("\r\n", "#").Trim('#').Replace("#", varrcap[4])
                         + varrcap[4] + "return (command.ExecuteNonQuery() != 0);"
                     + varrcap[3] + "}"
                     + varrcap[3] + "catch (NpgsqlException exception)"
                     + varrcap[3] + "{"
                         + varrcap[4] + "ConnectDatabase.TrackingError(exception.ToString(), \"Update(NpgsqlConnection connect)\", \"" + vdto + ".cs\");"
                         + varrcap[4] + "return false;"
                     + varrcap[3] + "}"
                 + varrcap[2] + "}";
            vsw.Write(vtext); vtext = "";
            #endregion

            #region delete
            vtext += varrcap[2] + "public bool Delete()"
                 + varrcap[2] + "{"
                    + varrcap[3] + "bool flag;"
                     + varrcap[3] + "try"
                     + varrcap[3] + "{"
                         + varrcap[4] + "this._connect = new NpgsqlConnection(this._condata.StringConnect);"
                         + varrcap[4] + "this._connect.Open();"
                         + varrcap[4] + "flag = this.Delete(this._connect);"
                     + varrcap[3] + "}"
                     + varrcap[3] + "catch (NpgsqlException exception)"
                     + varrcap[3] + "{"
                         + varrcap[4] + "ConnectDatabase.TrackingError(exception.ToString(), \"Delete()\", \"" + vdto + ".cs\");"
                         + varrcap[4] + "flag = false;"
                     + varrcap[3] + "}"
                     + varrcap[3] + "finally"
                     + varrcap[3] + "{"
                         + varrcap[4] + "this._connect.Close();"
                         + varrcap[4] + "this._connect.Dispose();"
                     + varrcap[3] + "}"
                     + varrcap[3] + "return flag;"
                 + varrcap[2] + "}";
            vsw.Write(vtext); vtext = "";
            vtext += varrcap[2] + "public bool Delete(NpgsqlConnection connect)"
                 + varrcap[2] + "{"
                     + varrcap[3] + "try"
                     + varrcap[3] + "{"
                        + varrcap[4] + "string vsql = \"delete from \" + this._sSchema + \".\" + tableName + \" where 1=0 \";";           
            
            vtext += varrcap[4] + "NpgsqlCommand command = new NpgsqlCommand(vsql, connect);"                       
                         + varrcap[4] + "return (command.ExecuteNonQuery() != 0);"
                     + varrcap[3] + "}"
                     + varrcap[3] + "catch (NpgsqlException exception)"
                     + varrcap[3] + "{"
                         + varrcap[4] + "ConnectDatabase.TrackingError(exception.ToString(), \"Delete(NpgsqlConnection connect)\", \"" + vdto + ".cs\");"
                         + varrcap[4] + "return false;"
                     + varrcap[3] + "}"
                 + varrcap[2] + "}";
            vsw.Write(vtext); vtext = "";
            #endregion

            #region Load
            vtext += varrcap[2] + "public void Load()"
                 + varrcap[2] + "{"                    
                     + varrcap[3] + "try"
                     + varrcap[3] + "{"
                         + varrcap[4] + "this._connect = new NpgsqlConnection(this._condata.StringConnect);"
                         + varrcap[4] + "this._connect.Open();"
                         + varrcap[4] + "this.Load(this._connect);"
                     + varrcap[3] + "}"
                     + varrcap[3] + "catch (NpgsqlException exception)"
                     + varrcap[3] + "{"
                         + varrcap[4] + "ConnectDatabase.TrackingError(exception.ToString(), \"Load()\", \"" + vdto + ".cs\");"                         
                     + varrcap[3] + "}"
                     + varrcap[3] + "finally"
                     + varrcap[3] + "{"
                         + varrcap[4] + "this._connect.Close();"
                         + varrcap[4] + "this._connect.Dispose();"
                     + varrcap[3] + "}"                     
                 + varrcap[2] + "}";
            vsw.Write(vtext); vtext = "";
            vtext += varrcap[2] + "public void Load(NpgsqlConnection connect)"
                 + varrcap[2] + "{"
                     + varrcap[3] + "try"
                     + varrcap[3] + "{";
                        //+ varrcap[4] + "string vsql = \"select * from \" + this._sSchema + \".\" + tableName + \" where 1=1 \";";
            vsInsCol = ""; vsInsVal = ""; vsInsPar = "";
            foreach (DataRow vdr in dtcolumn.Rows)
            {
                if (vdr["dat"].ToString() != "")
                {
                    vsInsCol += " + \",to_char(\" + column" + vdr["col"].ToString().ToUpper() + " + \",'dd/mm/yyyy hh24:mi') as \" + column" + vdr["col"].ToString().ToUpper();
                }
                else
                {
                    vsInsCol += " + \",\" + column" + vdr["col"].ToString().ToUpper();
                }
                
                if (vdr["valuetype"].ToString().ToLower() != "string")
                {
                    vsInsPar +=varrcap[4]+ "this."+vdr["value"].ToString()+" = "+vdr["valuetype"].ToString()+".Parse(vdt.Tables[0].Rows[0][column"+vdr["col"].ToString().ToUpper()+"].ToString());";
                }
                else
                {
                    vsInsPar += varrcap[4] + "this." + vdr["value"].ToString() + " = vdt.Tables[0].Rows[0][column" + vdr["col"].ToString().ToUpper() + "].ToString();";
                }
               
            }
            vtext += varrcap[4] + "DataSet vdt = new DataSet(); ";

            vtext += varrcap[4] + "string vsql = \"select \""+vsInsCol.Replace(" + \",\"","#").Trim('#').Replace("#"," + \",\"")
                + varrcap[4] + "+ \" from \" + this._sSchema + \".\" + tableName + \" where 1=1 \";";
            vtext += varrcap[4] + "NpgsqlDataAdapter command = new NpgsqlDataAdapter(vsql, connect);"
                         + varrcap[4] + "command.Fill(vdt);"
                         + varrcap[4] + "if(vdt.Tables[0].Rows.Count == 0) return;"
                         +vsInsPar
                     + varrcap[3] + "}"
                     + varrcap[3] + "catch (NpgsqlException exception)"
                     + varrcap[3] + "{"
                         + varrcap[4] + "ConnectDatabase.TrackingError(exception.ToString(), \"Load(NpgsqlConnection connect)\", \"" + vdto + ".cs\");"                         
                     + varrcap[3] + "}"
                 + varrcap[2] + "}";
            vsw.Write(vtext); vtext = "";
            #endregion

            #region LoadAll
            vtext += varrcap[2] + "public DataSet LoadAll()"
                 + varrcap[2] + "{"
                     + varrcap[3] + "try"
                     + varrcap[3] + "{"
                         + varrcap[4] + "this._connect = new NpgsqlConnection(this._condata.StringConnect);"
                         + varrcap[4] + "this._connect.Open();"
                         + varrcap[4] + "return this.LoadAll(this._connect);"
                     + varrcap[3] + "}"
                     + varrcap[3] + "catch (NpgsqlException exception)"
                     + varrcap[3] + "{"
                         + varrcap[4] + "ConnectDatabase.TrackingError(exception.ToString(), \"LoadAll()\", \"" + vdto + ".cs\");"
                         + varrcap[4] + "return null;"
                     + varrcap[3] + "}"
                     + varrcap[3] + "finally"
                     + varrcap[3] + "{"
                         + varrcap[4] + "this._connect.Close();"
                         + varrcap[4] + "this._connect.Dispose();"
                     + varrcap[3] + "}"
                 + varrcap[2] + "}";
            vsw.Write(vtext); vtext = "";
            vtext += varrcap[2] + "public DataSet LoadAll(NpgsqlConnection connect)"
                 + varrcap[2] + "{"
                     + varrcap[3] + "try"
                     + varrcap[3] + "{";            
            vsInsCol = ""; vsInsVal = ""; vsInsPar = "";
            foreach (DataRow vdr in dtcolumn.Rows)
            {
                if (vdr["dat"].ToString() != "")
                {
                    vsInsCol += " + \",to_char(\" + column" + vdr["col"].ToString().ToUpper() + " + \",'dd/mm/yyyy hh24:mi') as \" + column" + vdr["col"].ToString().ToUpper();
                }
                else
                {
                    vsInsCol += " + \",\" + column" + vdr["col"].ToString().ToUpper();
                }

            }
            vtext += varrcap[4] + "DataSet vdt = new DataSet(); ";

            vtext += varrcap[4] + "string vsql = \"select \"" + vsInsCol.Replace(" + \",\"", "#").Trim('#').Replace("#", " + \",\"")
                + varrcap[4] + "+ \" from \" + this._sSchema + \".\" + tableName + \" where 1=1 \";";
            vtext += varrcap[4] + "NpgsqlDataAdapter command = new NpgsqlDataAdapter(vsql, connect);"
                         + varrcap[4] + "command.Fill(vdt);"
                         + varrcap[4] + "return vdt;"
                         
                     + varrcap[3] + "}"
                     + varrcap[3] + "catch (NpgsqlException exception)"
                     + varrcap[3] + "{"
                         + varrcap[4] + "ConnectDatabase.TrackingError(exception.ToString(), \"LoadAll(NpgsqlConnection connect)\", \"" + vdto + ".cs\");"
                         + varrcap[4] + "return null;"
                     + varrcap[3] + "}"
                 + varrcap[2] + "}";
            vsw.Write(vtext); vtext = "";
            #endregion

            #region GetIDInsert
            vtext += varrcap[2] + "public int GetIDInsert()"
                 + varrcap[2] + "{"
                     + varrcap[3] + "try"
                     + varrcap[3] + "{"
                         + varrcap[4] + "this._connect = new NpgsqlConnection(this._condata.StringConnect);"
                         + varrcap[4] + "this._connect.Open();"
                         + varrcap[4] + "return this.GetIDInsert(this._connect);"
                     + varrcap[3] + "}"
                     + varrcap[3] + "catch (NpgsqlException exception)"
                     + varrcap[3] + "{"
                         + varrcap[4] + "ConnectDatabase.TrackingError(exception.ToString(), \"GetIDInsert()\", \"" + vdto + ".cs\");"
                         + varrcap[4] + "return 0;"
                     + varrcap[3] + "}"
                     + varrcap[3] + "finally"
                     + varrcap[3] + "{"
                         + varrcap[4] + "this._connect.Close();"
                         + varrcap[4] + "this._connect.Dispose();"
                     + varrcap[3] + "}"
                 + varrcap[2] + "}";
            vsw.Write(vtext); vtext = "";
            vtext += varrcap[2] + "public int GetIDInsert(NpgsqlConnection connect)"
                 + varrcap[2] + "{"
                     + varrcap[3] + "try"
                     + varrcap[3] + "{";
           
            vtext += varrcap[4] + "DataSet vdt = new DataSet(); ";

            vtext += varrcap[4] + "string vsql = \"select max(id) as idmax\""
                + varrcap[4] + "+ \" from \" + this._sSchema + \".\" + tableName + \" where 1=1 \";";
            vtext += varrcap[4] + "NpgsqlDataAdapter command = new NpgsqlDataAdapter(vsql, connect);"
                         + varrcap[4] + "command.Fill(vdt);"
                         + varrcap[4] + "int num=0;"
                         + varrcap[4] + "try{"
                         + varrcap[4] + "num = int.Parse(vdt.Tables[0].Rows[0][0].ToString());"
                         + varrcap[4] + "}catch{}"
                         + varrcap[4] + "return ++num;"
                     + varrcap[3] + "}"
                     + varrcap[3] + "catch (NpgsqlException exception)"
                     + varrcap[3] + "{"
                         + varrcap[4] + "ConnectDatabase.TrackingError(exception.ToString(), \"GetIDInsert(NpgsqlConnection connect)\", \"" + vdto + ".cs\");"
                         + varrcap[4] + "return 1;"
                     + varrcap[3] + "}"
                 + varrcap[2] + "}";
            vsw.Write(vtext); vtext = "";
            #endregion
            vtext += varrcap[1] + "}";
            vtext += varrcap[0] + "}";
            vsw.Write(vtext);
            vsw.Close();
        }

        private void frmcreatecode_FormClosing(object sender, FormClosingEventArgs e)
        {
            f_create_xml(true);
        }
    }
}
